/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.ihale.database;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.ihale.Ihale;
import org.ihale.satinalmaci.SatinAlmaci;
import org.ihale.tedarikci.Tedarikci;
import org.ihale.yonetici.Admin;

/**
 *
 * @author Emre
 */
public class DatabaseEmre {

    private Connection connection = null;
    private final String driver = "com.mysql.jdbc.Driver";
    private final String connectionString = "jdbc:mysql://localhost:3306/mydb";
    private final String user = "root";
    private final String password = "1234";

    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        connection = (Connection) DriverManager.getConnection(connectionString, user, password);
        System.out.println("Jdbc Mysql Connection String :");
        System.out.println(connection);


    }

    /**
     * Disconnection method for disconnecting the database
     * @throws SQLException Disconnect sql exception
     */
    public void disconnect() throws SQLException {
        if ((this.connection != null)) {
            if (!this.connection.isClosed()) {
                this.connection.close();
            }
        }
    }
//    
//    SELECT * FROM mydb.ihale where baslama_tarihi>'2011-12-01'
//SELECT * FROM mydb.ihale where bitis_tarihi<'2011-12-01'
//SELECT * FROM mydb.ihale where baslama_tarihi>='2011-12-27' and baslama_tarihi<='2011-12-31'
    
     public List<Ihale> listeleIhaleSimdiki() {
        List<Ihale> ihaleler = new ArrayList<Ihale>();
        try {
            Timestamp ts = new Timestamp((new java.util.Date().getTime()));
            String query = "SELECT * FROM mydb.ihale where baslama_tarihi<='" + ts.toString() + "' and bitis_tarihi>='" + ts.toString() +"'";
            Statement stmt = (Statement) connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            Ihale ihale;
            java.util.Date baslamaTarihi;
            java.util.Date bitisTarihi;
            String baslangicSaati;
            String bitisSaati;
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            while (rs.next()) {
                Timestamp tba = rs.getTimestamp("baslama_tarihi");
                //baslamaTarihi = new java.util.Date(tba.getYear(),tba.getMonth(),tba.getDate());
                baslamaTarihi = new java.util.Date(tba.getTime());
                Timestamp tbi = rs.getTimestamp("bitis_tarihi");
                // bitisTarihi = new java.util.Date(tbi.getYear(),tbi.getMonth(),tbi.getDate());
                bitisTarihi = new java.util.Date(tbi.getTime());
                baslangicSaati = Integer.toString(tba.getHours()) + ":" + Integer.toString(tba.getMinutes());
                bitisSaati = Integer.toString(tbi.getHours()) + ":" + Integer.toString(tbi.getMinutes());
                satinAlmaci.setId(rs.getInt("sat_almaci_id"));
                satinAlmaci = getirSatinAlmaci(satinAlmaci);
                ihale = new Ihale(rs.getInt("idihale"), rs.getString("aciklama"), baslamaTarihi, bitisTarihi, satinAlmaci, rs.getString("ihale_adi"), baslangicSaati, bitisSaati);
                ihaleler.add(ihale);
            }


        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ihaleler;

    }
     public List<Ihale> listeleIhaleGelecek() {
        List<Ihale> ihaleler = new ArrayList<Ihale>();
        try {
            String query = "Select * from ihale";
            Statement stmt = (Statement) connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            Ihale ihale;
            java.util.Date baslamaTarihi;
            java.util.Date bitisTarihi;
            String baslangicSaati;
            String bitisSaati;
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            while (rs.next()) {
                Timestamp tba = rs.getTimestamp("baslama_tarihi");
                //baslamaTarihi = new java.util.Date(tba.getYear(),tba.getMonth(),tba.getDate());
                baslamaTarihi = new java.util.Date(tba.getTime());
                Timestamp tbi = rs.getTimestamp("bitis_tarihi");
                // bitisTarihi = new java.util.Date(tbi.getYear(),tbi.getMonth(),tbi.getDate());
                bitisTarihi = new java.util.Date(tbi.getTime());
                baslangicSaati = Integer.toString(tba.getHours()) + ":" + Integer.toString(tba.getMinutes());
                bitisSaati = Integer.toString(tbi.getHours()) + ":" + Integer.toString(tbi.getMinutes());
                satinAlmaci.setId(rs.getInt("sat_almaci_id"));
                satinAlmaci = getirSatinAlmaci(satinAlmaci);
                ihale = new Ihale(rs.getInt("idihale"), rs.getString("aciklama"), baslamaTarihi, bitisTarihi, satinAlmaci, rs.getString("ihale_adi"), baslangicSaati, bitisSaati);
                ihaleler.add(ihale);
            }


        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ihaleler;

    }
     
     public List<Ihale> listeleIhaleGecmis() {
        List<Ihale> ihaleler = new ArrayList<Ihale>();
        try {
            String query = "Select * from ihale";
            Statement stmt = (Statement) connection.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            Ihale ihale;
            java.util.Date baslamaTarihi;
            java.util.Date bitisTarihi;
            String baslangicSaati;
            String bitisSaati;
            SatinAlmaci satinAlmaci = new SatinAlmaci();
            while (rs.next()) {
                Timestamp tba = rs.getTimestamp("baslama_tarihi");
                //baslamaTarihi = new java.util.Date(tba.getYear(),tba.getMonth(),tba.getDate());
                baslamaTarihi = new java.util.Date(tba.getTime());
                Timestamp tbi = rs.getTimestamp("bitis_tarihi");
                // bitisTarihi = new java.util.Date(tbi.getYear(),tbi.getMonth(),tbi.getDate());
                bitisTarihi = new java.util.Date(tbi.getTime());
                baslangicSaati = Integer.toString(tba.getHours()) + ":" + Integer.toString(tba.getMinutes());
                bitisSaati = Integer.toString(tbi.getHours()) + ":" + Integer.toString(tbi.getMinutes());
                satinAlmaci.setId(rs.getInt("sat_almaci_id"));
                satinAlmaci = getirSatinAlmaci(satinAlmaci);
                ihale = new Ihale(rs.getInt("idihale"), rs.getString("aciklama"), baslamaTarihi, bitisTarihi, satinAlmaci, rs.getString("ihale_adi"), baslangicSaati, bitisSaati);
                ihaleler.add(ihale);
            }


        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ihaleler;

    }
     
     public List<Tedarikci> listeTedarikciToIhale(int ihale_id) throws SQLException {
        String query = "select * from ted_ih inner join tedarikci on ted_ih.ted_id=tedarikci.idtedarikci where ted_ih.ih_id="+ihale_id;
        List<Tedarikci> TedarikciListesi = new ArrayList<Tedarikci>();

        try {
            Statement statement = (Statement) this.connection.createStatement();
            ResultSet result = statement.executeQuery(query);
            while (result.next()) {
                Tedarikci tedarikci = new Tedarikci();
                tedarikci.setId(result.getInt("idtedarikci"));
                tedarikci.setSirketAdi(result.getString("sirket_adi"));
                tedarikci.setAdres(result.getString("adres_tel"));
                tedarikci.setWebSite(result.getString("websitesi"));
                tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
                tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
                tedarikci.setEposta(result.getString("eposta"));
                TedarikciListesi.add(tedarikci);
            }
            return TedarikciListesi;
        } catch (SQLException e) {
            return TedarikciListesi;
        }


    }
     
     
     public SatinAlmaci getirSatinAlmaci(SatinAlmaci satinAlmaci) {
        SatinAlmaci satinAlmaci2 = new SatinAlmaci();
        try {

            String query = "Select * from satinalmaci where idsatinalmaci = ?";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(query);
            stmt.setInt(1, satinAlmaci.getId());
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                satinAlmaci2 = new SatinAlmaci(rs.getInt("idsatinalmaci"), rs.getString("sirket_adi"), rs.getString("adres_tel"), rs.getString("websitesi"), rs.getString("ilgili_kisi"), rs.getString("sifre"), rs.getString("kullanici_adi"), rs.getString("eposta"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return satinAlmaci2;
    }

    public Tedarikci getirTedarikciLogin(String kullanici_adi, String sifre) throws SQLException {
        String query = "SELECT idtedarikci, sirket_adi,adres_tel,websitesi,ilgili_kisi,kullanici_adi FROM  tedarikci where (kullanici_adi=? and sifre=?)";
        Tedarikci tedarikci = new Tedarikci();
        try {
            PreparedStatement commit = (PreparedStatement) this.connection.prepareStatement(query);
            commit.setString(1, kullanici_adi);
            commit.setString(2, sifre);
            ResultSet result = commit.executeQuery();
            if (result.next()) {
                tedarikci.setId(result.getInt("idtedarikci"));
                tedarikci.setSirketAdi(result.getString("sirket_adi"));
                tedarikci.setAdres(result.getString("adres_tel"));
                tedarikci.setWebSite(result.getString("websitesi"));
                tedarikci.setIlgiliKisi(result.getString("ilgili_kisi"));
                tedarikci.setKullaniciAdi(result.getString("kullanici_adi"));
                //tedarikci.setEposta(result.getString("eposta"));
            }
        } catch (SQLException e) {
            throw e;
        }
        return tedarikci;
    }

    public Admin getirAdminLogin(String kullanici_adi, String sifre) throws SQLException {
        Admin admin = new Admin();
        String query = "Select * from admin where (kullanici_adi=? and sifre=?)";
        try {
            PreparedStatement commit = (PreparedStatement) this.connection.prepareStatement(query);
            commit.setString(1, kullanici_adi);
            commit.setString(2, sifre);
            ResultSet result = commit.executeQuery();
            if (result.next()) {
                admin.setKullaniciAdi(result.getString("kullanici_adi"));
                admin.setSifre(result.getString("sifre"));
            }
        } catch (SQLException e) {
            throw e;
        }
        return admin;
    }

    public SatinAlmaci getirSatinAlmacilogin(String kullanici_adi, String sifre) {
        SatinAlmaci satinAlmaci = new SatinAlmaci();
        try {

            String query = "Select * from satinalmaci where (kullanici_adi = ? and sifre = ?)";
            PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(query);
            stmt.setString(1, kullanici_adi);
            stmt.setString(2, sifre);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                satinAlmaci = new SatinAlmaci(rs.getInt("idsatinalmaci"), rs.getString("sirket_adi"), rs.getString("adres_tel"), rs.getString("websitesi"), rs.getString("ilgili_kisi"), rs.getString("sifre"), rs.getString("kullanici_adi"), rs.getString("eposta"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return satinAlmaci;
    }
}
